package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.Pergunta;
import bean.Usuario;

public class SqlServer {
	private String url = "jdbc:sqlserver://localhost:1433; databaseName=dbQuiz;";
	private String user = "sa";
	private String senha = "mhcdx9";
	private String clazzForName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private Connection conexao = null;
	
	
	public boolean validarLogin(String usuario, String senha){
		boolean autenticado = false;		
		try{
			Class.forName(clazzForName);			
		}catch (ClassNotFoundException e){
			e.printStackTrace();
		}
		try {
			conexao = DriverManager.getConnection(url, user, this.senha);
			String sql = "exec spAutenticar ?, ?";

			PreparedStatement pstmt = conexao.prepareStatement(sql);
			pstmt.setString(1, usuario);
			pstmt.setString(2, senha);			
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				return true;
			}
			rs.close();
			pstmt.close();
			conexao.close();
		}catch (SQLException e){
			e.printStackTrace();
		}
			
		return false;
	}
	
	public boolean cadastrarUsuario(String usuario, String senha){
		int retorno = 0;
		try{			
			Class.forName(clazzForName);			
			Connection conn = DriverManager.getConnection(url,user,this.senha);			
			String sql = "exec spCadastrarUsuario ?, ?";
			
			PreparedStatement st = conn.prepareStatement(sql);
			
			st.setString(1, usuario);
			st.setString(2, senha);
			
			retorno = st.executeUpdate();	
			
		}catch(SQLException ex){
			System.out.println(ex.getMessage());
		}catch(ClassNotFoundException ex){
			System.out.println(ex.getMessage());
		}
		if(retorno == 1){
			return true;
		}else{
			return false;
		}
	}
	
	public int getPontos(String usuario){
		return 0;
	}
	
	public boolean cadastrarPergunta(Pergunta pergunta){
		int retorno = 0;
		try{			
			Class.forName(clazzForName);			
			Connection conn = DriverManager.getConnection(url,user,this.senha);			
			String sql = "exec spCadastrarPergunta ?, ?, ?, ?";
			
			PreparedStatement st = conn.prepareStatement(sql);
			
			st.setString(1, pergunta.getPergunta());
			st.setString(2, pergunta.getResposta());
			st.setString(3, pergunta.getAlternativas());
			st.setString(4, pergunta.getFoto());
			
			retorno = st.executeUpdate();	
			
		}catch(SQLException ex){
			System.out.println(ex.getMessage());
		}catch(ClassNotFoundException ex){
			System.out.println(ex.getMessage());
		}
		if(retorno == 1){
			return true;
		}else{
			return false;
		}
	}
	
	public ArrayList<Usuario> getUsuarios(){
		ArrayList<Usuario> retorno = new ArrayList<Usuario>();
		try{
			Class.forName(clazzForName);			
		}catch (ClassNotFoundException e){
			e.printStackTrace();
		}
		try {
			conexao = DriverManager.getConnection(url, user, this.senha);
			String sql = "exec spGetUsuarios";

			PreparedStatement pstmt = conexao.prepareStatement(sql);	
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {

				Usuario usu = new Usuario(rs.getString("usuario"), rs.getInt("pontos"));
				
				retorno.add(usu);

				
			}
			pstmt.close();
			rs.close();
			conexao.close();
		}catch (SQLException e){
			e.printStackTrace();
		}
			
		return retorno;
	}
	
	public boolean updatePontos(String usuario, int pontos){
		int retorno = 0;
		try{			
			Class.forName(clazzForName);			
			Connection conn = DriverManager.getConnection(url,user,this.senha);			
			String sql = "exec spUpdatePontos ?, ?";
			
			PreparedStatement st = conn.prepareStatement(sql);
			
			st.setString(1, usuario);
			st.setString(2, pontos+"");
			
			retorno = st.executeUpdate();	
			
		}catch(SQLException ex){
			System.out.println(ex.getMessage());
		}catch(ClassNotFoundException ex){
			System.out.println(ex.getMessage());
		}
		if(retorno == 1){
			return true;
		}else{
			return false;
		}
	}
	
	public ArrayList<Pergunta> getPerguntas(){
		ArrayList<Pergunta> retorno = new ArrayList<Pergunta>();
		try{
			Class.forName(clazzForName);			
		}catch (ClassNotFoundException e){
			e.printStackTrace();
		}
		try {
			conexao = DriverManager.getConnection(url, user, this.senha);
			String sql = "exec spGetPerguntas";

			PreparedStatement pstmt = conexao.prepareStatement(sql);	
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				Pergunta per = new Pergunta(rs.getString("pergunta"), rs.getString("resposta"), rs.getString("alternativas"), rs.getString("foto"));				
				retorno.add(per);			
			}
			pstmt.close();
			rs.close();
			conexao.close();
		}catch (SQLException e){
			e.printStackTrace();
		}
			
		return retorno;
	}
}
